
%macro get_empl_incm;

	proc printto new log = "&logdir./get_empl_incm.txt";
	run;
	
	proc sort data = rlib.own_w2(keep=person_id year) 
		out = touse
	 	nodupkey;
	 	by person_id year;
	run;
	  
	
		data _null_;
			set touse;
			file "&tmpdir./tinlist" dlm=',';
			put person_id year;
		run;



		/****************************************************
		*													*
		*					Main SQL call					*
		*													*
		****************************************************/
		proc sql;
		[redacted]
			(
	
				
				select
					max(a.[redacted]) as inc,
					max(a.[redacted]) as fs,
					b.person_id,
					b.year
				into #prim
				from [redacted] as a
					inner join #tinlist as b
						on(a.[redacted] = b.person_id and a.[redacted] = b.year)
				group by b.person_id,
					b.year
				
				
				
				select
					max(a.[redacted]) as inc,
					max(b.[redacted]) as fs,
					c.person_id,
					c.year
				into #sec
				from [redacted] as a
					inner join [redacted] as b
						on(a.[redacted] = b.[redacted] and a.[redacted] = b.[redacted])
					inner join #tinlist as c
						on(b.[redacted] = c.person_id and b.[redacted] = c.year)
				where a.[redacted] = 2
				group by c.person_id, 
					c.year
				
				select
					inc, person_id, fs, year
				into #t1
				from #prim
				union
				select
					inc, person_id, fs, year
				from #sec
				
				
				
				
				
				select
					max(inc) as inc_f1040,
					max(fs) as fs,
					person_id,
					year
				into #f1040
				from #t1
				group by person_id, 
					year
				
				
				
				select 
					sum(coalesce(a.[redacted],0) + coalesce(a.[redacted],0)) as wgs,
					b.person_id,
					b.year
				into #wages
				from irmf_w2 as a
					inner join #tinlist as b
						on(a.[redacted] = b.person_id and a.[redacted] = b.year)
				where a.[redacted] <= 4
				group by b.person_id, b.year
				
				
				
				
				
				
				select
					(case when b.fs = 2 then b.inc_f1040/sqrt(2)
						when b.fs is not null then b.inc_f1040
						else a.wgs end) as master_income,
					a.person_id,
					a.year
				from #wages as a
					left outer join #f1040 as b
						on(a.person_id = b.person_id and a.year = b.year)

								
				

		);
	disconnect from iq;
	quit;


	

	proc printto;
	run;
	
	
%mend;

